#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
Access数据库管理模块
提供Access数据库的创建、连接、表操作、数据增删查改等功能
"""

import os
import sys

try:
    import pyodbc
    import win32com.client
except ImportError as e:
    print(f"❌ 缺少依赖库: {e}")
    sys.exit(1)


class AccessDBManager:
    """Access数据库管理器"""
    
    def __init__(self, db_path):
        self.db_path = os.path.abspath(db_path)
        self.connection = None
        self.cursor = None
    
    def create_database(self):
        """创建Access数据库文件"""
        try:
            if os.path.exists(self.db_path):
                os.remove(self.db_path)
            
            dao_engine = win32com.client.Dispatch("DAO.DBEngine.120")
            db = dao_engine.CreateDatabase(self.db_path, ";LANGID=0x0804;CP=936;COUNTRY=0")
            db.Close()
            return True
        except Exception as e:
            print(f"❌ 数据库创建失败: {e}")
            return False
    
    def connect(self):
        """连接到数据库"""
        try:
            conn_str = f"Driver={{Microsoft Access Driver (*.mdb, *.accdb)}};Dbq={self.db_path};"
            self.connection = pyodbc.connect(conn_str)
            self.cursor = self.connection.cursor()
            return True
        except Exception as e:
            print(f"❌ 数据库连接失败: {e}")
            return False
    
    def create_table(self, table_name, columns):
        """创建数据表"""
        try:
            column_defs = [f"[{col_name}] {col_type}" for col_name, col_type in columns.items()]
            sql = f"CREATE TABLE [{table_name}] ({', '.join(column_defs)})"
            self.cursor.execute(sql)
            self.connection.commit()
            return True
        except Exception as e:
            print(f"❌ 表创建失败: {e}")
            return False
    
    def insert_data(self, table_name, data):
        """插入批量数据"""
        try:
            if not data:
                return True
            
            placeholders = ', '.join(['?' for _ in data[0]])
            sql = f"INSERT INTO [{table_name}] VALUES ({placeholders})"
            self.cursor.executemany(sql, data)
            self.connection.commit()
            return True
        except Exception as e:
            print(f"❌ 数据插入失败: {e}")
            return False
    
    def insert_record(self, table_name, record_dict):
        """插入单条记录"""
        try:
            columns = list(record_dict.keys())
            values = list(record_dict.values())
            
            column_names = ', '.join([f'[{col}]' for col in columns])
            placeholders = ', '.join(['?' for _ in columns])
            
            sql = f"INSERT INTO [{table_name}] ({column_names}) VALUES ({placeholders})"
            self.cursor.execute(sql, values)
            self.connection.commit()
            return True
        except Exception as e:
            print(f"❌ 记录插入失败: {e}")
            return False
    
    def select_data(self, table_name, conditions=None, columns="*", limit=None):
        """查询数据"""
        try:
            sql = f"SELECT {columns} FROM [{table_name}]"
            
            if conditions:
                where_clause = ' AND '.join([f"[{k}] = ?" for k in conditions.keys()])
                sql += f" WHERE {where_clause}"
                values = list(conditions.values())
            else:
                values = []
            
            if limit:
                sql += f" TOP {limit}"
            
            self.cursor.execute(sql, values)
            rows = self.cursor.fetchall()
            
            # 获取列名
            column_names = [column[0] for column in self.cursor.description]
            
            # 转换为字典列表
            result = []
            for row in rows:
                result.append(dict(zip(column_names, row)))
            
            return result
        except Exception as e:
            print(f"❌ 数据查询失败: {e}")
            return []
    
    def update_data(self, table_name, update_dict, conditions):
        """更新数据"""
        try:
            if not update_dict or not conditions:
                return False
            
            # 构建SET子句
            set_clause = ', '.join([f"[{k}] = ?" for k in update_dict.keys()])
            
            # 构建WHERE子句
            where_clause = ' AND '.join([f"[{k}] = ?" for k in conditions.keys()])
            
            sql = f"UPDATE [{table_name}] SET {set_clause} WHERE {where_clause}"
            
            # 合并参数值
            values = list(update_dict.values()) + list(conditions.values())
            
            self.cursor.execute(sql, values)
            affected_rows = self.cursor.rowcount
            self.connection.commit()
            
            return True
        except Exception as e:
            print(f"❌ 数据更新失败: {e}")
            return False
    
    def delete_data(self, table_name, conditions):
        """删除数据"""
        try:
            if not conditions:
                print("❌ 删除操作必须指定条件")
                return False
            
            where_clause = ' AND '.join([f"[{k}] = ?" for k in conditions.keys()])
            sql = f"DELETE FROM [{table_name}] WHERE {where_clause}"
            
            values = list(conditions.values())
            
            self.cursor.execute(sql, values)
            affected_rows = self.cursor.rowcount
            self.connection.commit()
            
            return True
        except Exception as e:
            print(f"❌ 数据删除失败: {e}")
            return False
    
    def count_records(self, table_name, conditions=None):
        """统计记录数"""
        try:
            sql = f"SELECT COUNT(*) FROM [{table_name}]"
            
            if conditions:
                where_clause = ' AND '.join([f"[{k}] = ?" for k in conditions.keys()])
                sql += f" WHERE {where_clause}"
                values = list(conditions.values())
            else:
                values = []
            
            self.cursor.execute(sql, values)
            count = self.cursor.fetchone()[0]
            return count
        except Exception as e:
            print(f"❌ 统计记录失败: {e}")
            return 0
    
    def table_exists(self, table_name):
        """检查表是否存在"""
        try:
            # 尝试查询表的第一行，如果表存在则成功，不存在则抛出异常
            sql = f"SELECT TOP 1 * FROM [{table_name}]"
            self.cursor.execute(sql)
            return True
        except Exception:
            # 如果查询失败，说明表不存在
            return False
    
    def get_table_structure(self, table_name):
        """获取表结构"""
        try:
            sql = f"SELECT * FROM [{table_name}] WHERE 1=0"
            self.cursor.execute(sql)
            
            columns = []
            for column in self.cursor.description:
                columns.append({
                    'name': column[0],
                    'type': column[1].__name__ if column[1] else 'unknown',
                    'size': column[2] if column[2] else None
                })
            
            return columns
        except Exception as e:
            print(f"❌ 获取表结构失败: {e}")
            return []
    
    def close(self):
        """关闭数据库连接"""
        try:
            if self.cursor:
                self.cursor.close()
            if self.connection:
                self.connection.close()
        except:
            pass